package ch.fhnw.apsi.db;

import static ch.fhnw.apsi.beans.BeanHelper.beanToMap;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.security.SecureRandom;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.Map;
import java.util.SortedMap;
import java.util.TreeMap;

import javax.annotation.Nullable;

import ch.fhnw.apsi.beans.Credentials;
import ch.fhnw.apsi.beans.Firma;
import ch.fhnw.apsi.password.PasswortGenerator;

public class MySQLDB implements DB {

  private final Connection connection;
  private final PreparedStatement stmtPLZ;
  private final PreparedStatement stmtGetFirma;
  private final PreparedStatement stmtUsername;
  private final PreparedStatement stmtGetCred;

  public MySQLDB() throws SQLException {
    try {
      Class.forName("com.mysql.jdbc.Driver");
    } catch (final ClassNotFoundException e) {
      throw new SQLException("", e);
    }
    this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/apsi", "apsi", "password");
    this.stmtPLZ = this.connection
        .prepareStatement("SELECT COUNT(*) as count FROM plz WHERE plz.plz = ? AND plz.ort = ?");
    this.stmtGetFirma = this.connection.prepareStatement("SELECT * FROM firma where id = ?");
    this.stmtUsername = this.connection
        .prepareStatement("SELECT COUNT(*) as count FROM credentials WHERE username = ?");
    this.stmtGetCred = this.connection.prepareStatement("SELECT * FROM credentials where username = ?");
  }

  @Override
  public boolean checkPLZ(final int plz, final String ort) throws SQLException {
    this.stmtPLZ.setInt(1, plz);
    this.stmtPLZ.setString(2, ort);
    if (!this.stmtPLZ.execute())
      return false;
    try (ResultSet resultSet = this.stmtPLZ.getResultSet()) {
      return resultSet.first() && resultSet.getInt(1) > 0;
    }
  }

  /**
   * Benutzer Spitzname (user ID): min. 4 Zeichen, max. 64 Zeichen; Erlaubte Zeichen: Zahlen, Gross- und
   * Klein-Buchstaben mit Umlauten, Punkte, Bindestriche, Unterstriche;
   * 
   * @param firma
   * @param count
   * @return
   */
  private static String createUsername(final Firma firma, final int count) {
    String username = firma.getFirmenname();
    final int min = 4, max = 64;
    if (username.length() < min)
      username += firma.getStadt();
    if (username.length() < min)
      username += firma.getStrasse();
    username = username.trim().replaceAll("[^" + Firma.BUCHSTABEN + "]+", "");
    if (username.length() < min)
      username += new SecureRandom().nextInt(90000) + 10000;
    if (username.length() < min)
      throw new RuntimeException();
    if (username.length() > max)
      username = username.substring(0, max);
    if (count > 0)
      username = username + count;
    return username;
  }

  private boolean containsUsername(final String username) throws SQLException {
    this.stmtUsername.setString(1, username);
    if (!this.stmtUsername.execute())
      return false;
    try (ResultSet resultSet = this.stmtUsername.getResultSet()) {
      return resultSet.first() && resultSet.getInt(1) > 0;
    }
  }

  private static String createInsertStatement(final SortedMap<String, ?> map, final String table) {
    final StringBuilder sql = new StringBuilder("INSERT INTO ").append(table).append(" (");
    for (final String key : map.keySet())
      sql.append(key).append(",");
    sql.replace(sql.length() - 1, sql.length(), ")");
    sql.append(" VALUES (");
    for (int i = 0; i < map.values().size() - 1; i++)
      sql.append("?,");
    sql.append("?)");
    return sql.toString();
  }

  @Override
  public Credentials createFirma(final Firma firma, final char[] password) throws SQLException, RuntimeException {
    synchronized (MySQLDB.class) {
      if (firma.getId() > 0)
        throw new RuntimeException("Firma ist nicht neu!");
      this.connection.setAutoCommit(false);
      this.connection.clearWarnings();
      final Savepoint savepoint = this.connection.setSavepoint();
      try {
        // Note: the table has a constraint that the company name needs to be unique!
        int count = 0;
        String name;
        do
          name = createUsername(firma, count++);
        while (this.containsUsername(name));
        final SortedMap<String, Object> map = beanToMap(firma);
        map.remove("id"); // there is also an annotation @Id for that, but I know it's "id"
        final String sql = createInsertStatement(map, "firma");
        final Credentials creds;
        try (PreparedStatement stmt = this.connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
          final long firmaId;
          int i = 1;
          // Note: The Map is a TreeMap and it keeps order.
          for (final Object val : map.values())
            stmt.setObject(i++, val);
          if (stmt.executeUpdate() != 1)
            throw new SQLException("Nothing inserted.");// Maybe it already existed.
          try (ResultSet keys = stmt.getGeneratedKeys()) {
            keys.first();
            firmaId = keys.getLong(1); // Name is "GENERATED_KEY"
          }
          PasswortGenerator.randomPasswort(password);
          creds = new Credentials(name, password, true, firmaId);

          final SortedMap<String, Object> map2 = beanToMap(creds);
          final String sql2 = createInsertStatement(map2, "credentials");
          i = 1;
          try (PreparedStatement stmt2 = this.connection.prepareStatement(sql2)) {
            for (final Object val : map2.values())
              stmt2.setObject(i++, val);
            if (stmt2.executeUpdate() != 1)
              throw new SQLException("Nothing inserted.");// Maybe it already existed.
          }
        }
        assert creds != null;
        assert this.connection.getWarnings() == null;
        this.connection.commit();
        assert this.connection.getWarnings() == null;
        return creds;
      } catch (final Throwable t) {
        this.connection.rollback(savepoint);
        throw t;
      } finally {
        this.connection.setAutoCommit(true);
      }
    }
  }

  @Override
  public void updateFirma(final Firma firma) throws SQLException {
    if (firma.getId() <= 0)
      throw new RuntimeException("Firma ist neu! -> Insert statt Update.");
    // Note: the table has a constraint that the company name needs to be unique!
    int count = 0;
    String name;
    do
      name = createUsername(firma, count++);
    while (this.containsUsername(name));
    final Map<String, Object> map = beanToMap(firma);
    map.remove("id"); // there is also an annotation @Id for that, but I know it's "id"
    final StringBuilder sql = new StringBuilder("UPDATE firma (");
    for (final String key : map.keySet())
      sql.append(key).append(",");
    sql.replace(sql.length() - 1, sql.length(), ")");
    sql.append(" VALUES ( ?");
    for (int i = 0; i < map.values().size() - 1; i++)
      sql.append("?,");
    try (PreparedStatement stmt = this.connection.prepareStatement(sql.toString())) {
      int i = 1;
      // Note: map is a TreeMap and it keeps order.
      for (final Object val : map.values())
        stmt.setObject(i++, val);
      if (stmt.executeUpdate() != 1)
        throw new SQLException("Nothing inserted.");
      try (ResultSet keys = stmt.getGeneratedKeys()) {
        keys.first();
      }
    }
  }

  @Override
  public Firma getFirma(final long id) throws SQLException {
    this.stmtGetFirma.setLong(1, id);
    final Firma firma = new Firma();
    try (final ResultSet resultSet = this.stmtGetFirma.executeQuery()) {
      try {
        final BeanInfo beanInfo = Introspector.getBeanInfo(Firma.class);
        final PropertyDescriptor[] descriptors = beanInfo.getPropertyDescriptors();
        final Map<String, Object> map = new TreeMap<>();
        for (final PropertyDescriptor propDesc : descriptors) {
          if (propDesc.getReadMethod().getAnnotation(Id.class) != null)
            continue;
          final Method setter = propDesc.getWriteMethod();
          setter.invoke(firma, resultSet.getObject(propDesc.getName()));
          map.put(propDesc.getName(), resultSet.getObject(propDesc.getName()));
        }
      } catch (final Exception cause) {
        // use spring framework with JPA if you want something better than this!
        if (cause instanceof InvalidInputException)
          throw new SQLException("Existing Data is invalid. Record must be fixed directly in database.", cause);
        throw new SQLException("Unknown database error. ", cause);
      }
    }
    return firma;
  }

  @Override
  public void deleteFirma(final Firma firma) throws SQLException {
    try (PreparedStatement ps = this.connection.prepareStatement("DELETE FROM firma WHERE id = ?")) {
      // Note: The foreign key constraints makes sure that the delete is cascaded to credentials!
      ps.setLong(1, firma.getId());
      ps.executeUpdate();
    }
  }

  @Override
  public boolean isValid() throws SQLException {
    return this.connection != null && this.connection.isValid(1000);
    // TODO this could also check that all needed tables exist.
    // However, that isn't exactly relevant for security.
  };

  @Override
  protected void finalize() throws Throwable {
    super.finalize();
    this.connection.close();
  }

  public static void main(final String[] args) throws SQLException, RuntimeException {
    final MySQLDB db = new MySQLDB();
    System.out.println(db.isValid());
    final Firma user = new Firma();
    try {
      user.setFirmenname("FHNW");
      user.setEmail("info@fhnw.ch");
      user.setPlz("5210");
      user.setStadt("Windisch");
      user.setStrasse("Klosterzelgstrasse 2");
      user.validate();
    } catch (final InvalidInputException e) {
      e.printStackTrace();
    }
    final char[] pw = new char[PasswortGenerator.LENGTH];
    final Credentials credentials = db.createFirma(user, pw);
    System.out.println("new username: " + credentials.getUsername());
    System.out.println("new password: " + String.valueOf(pw));
    PasswortGenerator.randomPasswort(pw);
    System.out.println(db.checkPLZ(4102, "Binningen"));
  }

  @Override
  @Nullable
  public Credentials getCredentials(final String username) throws SQLException {
    this.stmtGetCred.setString(1, username);
    try (final ResultSet resultSet = this.stmtGetCred.executeQuery()) {
      try {
        if (!resultSet.first())
          return null;
        final String hash = resultSet.getString("passwortHash");
        final boolean prov = resultSet.getBoolean("provisorilyPassword");
        final long firmaId = resultSet.getLong("firmaId");
        return new Credentials(username, hash, prov, firmaId);
      } catch (final Exception cause) {
        // use spring framework with JPA if you want something better than this!
        throw new SQLException("Unknown database error. ", cause);
      }
    }
  }

  @Override
  public void updateCredentials(final Credentials newCreds) throws SQLException {
    // Right now we only allow to update the password!
    try (PreparedStatement stmt = this.connection
        .prepareStatement("UPDATE Credentials SET passwortHash = ?, provisorilyPassword = 0 WHERE firmaId = ? AND username = ?")) {
      // Note: Both firmaId and Username are unique key of this DB.
      stmt.setString(1, newCreds.getPasswortHash());
      stmt.setLong(2, newCreds.getFirmaId());
      stmt.setString(3, newCreds.getUsername());
      if (1 != stmt.executeUpdate())
        throw new SQLException("Nothing was updated.");
    }
  }

}
